1. Technical Field
The present invention relates to a method and system for updating and deleting records in tables generally in a database management system, and particularly in a business data warehouse, due to changes in source files which are logically associated with the tables.
2. Related Art
Updating and deleting records in a large table in a business data warehouse can be prohibitively time consuming. Thus, there is a need for a method and system for efficiently updating and deleting records in a large table in a business data warehouse.
The present invention provides a system for updating a tableset T of a family, in relation to
N source files denoted as S1, S2, . . . , SN, said N at least 1, said tableset T having M tables T1, T2, . . . , TM, said M at least 1, said N source files logically associated with said M tables, said family having a common family key KF across the M tables and across the N source files,
a fileset SA comprising files S1A, S2A, . . . , SNA respectively embodying S1, S2, . . . , SN at a time tA such that T1, T2, . . . , TM at the time tA accurately reflects SA, and
a fileset SB comprising files S1B, S2B, . . . , SNB respectively embodying S1, S2, . . . , SN at a time tB, said tB and tA being different times,
the system comprises:
a Delta program adapted to generate a dataset D of non-redundant family keyvalues embodying KF, each said non-redundant family keyvalue drawn from selected Update records of S1B relative to S1A, said selected Update records not having any redundant family keyvalues, for i=1, 2, . . . , N;
a Trigger program adapted, for i=1, 2, . . . , N: to step through all records of S1B, including to compare the family keyvalue KFVR within each record of S1B against the family keyvalues in the dataset D and to save in a file xcex94Si each said record of SiB for which KFVR equals one of the family keyvalues KFV in the dataset D;
a Bridge program adapted to generate files xcex94T1, xcex94T2, . . . , xcex94TM that includes the data in xcex94S1, xcex94S2, . . . , xcex94SN in a form that is compatible with the M tables T1, T2, . . . , TM, respectively; and
at least one Load program adapted to update the tables T1, T2, . . . , TM with the data in xcex94T1, xcex94T2, . . . , xcex94TM, respectively.
The present invention provides a method for updating a tableset T of a family, in relation to
N source files denoted as S1, S2, . . . , SN, said N at least 1, said tableset T having M tables T1, T2, . . . , TM, said M at least 1, said N source files logically associated with said M tables, said family having a common family key KF across the M tables and across the N source files,
a fileset SA comprising files S1A, S2A, . . . , SNA respectively embodying S1, S2, . . . , SN at a time tA such that T1, T2, . . . , TM at the time tA accurately reflects SA, and
a fileset SB comprising files S1B, S2B, . . . , SNB respectively embodying S1, S2, . . . , SN at a time tB, said tB and tA being different times,
the method comprises:
executing a Delta program, including generating a dataset D of non-redundant family keyvalues embodying KF, each said non-redundant family keyvalue drawn from selected Update records of SiB relative to SiA, said selected Update records not having any redundant family keyvalues, for i=1, 2, . . . , N;
executing a Trigger program, including for i=1, 2, . . . , N: stepping through all records of SiB, including comparing the family keyvalue KFVR within each record of SiB against the family keyvalues in the dataset D and saving in a file xcex94Si each said record of SiB for which KFVR equals one of the family keyvalues KFV in the dataset D;
executing a Bridge program, including generating files xcex94T1, xcex94T2, . . . , xcex94TM that includes the data in xcex94S1, xcex94S2, . . . , xcex94SN in a form that is compatible with the M tables T1, T2, . . . , TM, respectively; and
executing at least one Load program, including updating the tables T1, T2, . . . , TM with the data in xcex94T1, xcex94T2, . . . , xcex94TM, respectively.
The present invention provides a system for deleting deletion-targeted records in a tableset T of a family, in relation to
N source files denoted as S1, S2, . . . , SN, said N at least 1, said tableset T having M tables T1, T2, . . . , TM, said M at least 1, said N source files logically associated with said M tables, said family having a common family key KF across the M tables and across the N source files,
a fileset SA comprising files S1A, S2A, . . . , SNA respectively embodying S1, S2, . . . , SN at a time tA such that T1, T2, . . . , TM at the time tA accurately reflects SA, and
a fileset SB comprising files S1B, S2B, . . . , SNB respectively embodying S1, S2, . . . , SN at a time tB, said tB and tA being different times,
the system comprises:
a Delta program adapted to generate a dataset DDEL of non-redundant family keyvalues embodying KF, each said non-redundant family keyvalue drawn from selected Delete records of SiB relative to SiA, said selected Delete records not having any redundant family keyvalues, for i=1,2, . . . , N; and
at least one Load program adapted to delete the deletion-targeted records in the tables T1, T2, . . . , TM, each said deletion-targeted record having one of the non-redundant family keyvalues that exists in the dataset DDEL.
The present invention provides a method for deleting deletion-targeted records in a tableset T of a family, in relation to
N source files denoted as S1, S2, . . . , SN, said N at least 1, said tableset T having M tables T1, T2, . . . , TM, said M at least 1, said N source files logically associated with said M tables, said family having a common family key KF across the M tables and across the N source files,
a fileset SA comprising files S1A, S2A, . . . , SNA respectively embodying S1, S2, . . . , SN at a time tA such that T1, T2, . . . , TM at the time tA accurately reflects SA, and
a fileset SB comprising files S1B, S2B, . . . , SNB respectively embodying S1, S2, . . . , SN at a time tB, said tB and tA being different times,
the method comprises:
executing a Delta program, including generating a dataset DDEL of non-redundant family keyvalues embodying KF, each said non-redundant family keyvalue drawn from selected Delete records of SiB relative to SiA, said selected Delete records not having any redundant family keyvalues, for i=1, 2, . . . , N; and
executing at least one Load program, including deleting the deletion-targeted records in the tables T1, T2, . . . , TM, each said deletion-targeted record having one of the non-redundant family keyvalues that exists in the dataset DDEL.
The present invention provides a method and associated system for efficiently updating and deleting records in a large table in a database of a database management system (DBMS) such as in a business data warehouse of a DBMS.